﻿using System;
using System.Collections.Generic;
using System.Text;
using XiaoGang.Soft.XGWMS.COM;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M012_MEMBER_SQL
    {
        #region SQL       

        #region Get User List

        private static string _getUserList_Sql = @"SELECT
    M012.USER_ID , 
    M012.USER_NO,
    M012.USER_NAME , 
    M012.USER_SEX AS USER_SEX_HIDE, 
    (CASE M012.USER_SEX WHEN '0' THEN '男' ELSE '女' END) AS USER_SEX, 
    M012.USER_BIRTHDAY , 
    M012.USER_PHONE1 , 
    M012.USER_PHONE2 , 
    M012.USER_ADDRESS1 , 
    M012.USER_ADDRESS2 , 
    M012.USER_EMAIL , 
    M012.USER_POST , 
    M012.USER_PHOTO , 
    M012.MEMO 
    ,((SELECT ISNULL(-SUM(SELLING_PRICE*UNITS),0) FROM M009_STORAGE 
WHERE BILL_ID IN (SELECT BILL_ID FROM M010_BILL WHERE USER_NO = M012.USER_ID and isnull(USER_NO,'')<>'' and SELLING_PRICE >0 ))  
+isnull( (SELECT CONVERT(MONEY,SUM(TOTAL)) FROM M014_MerberBuy
WHERE USER_ID = M012.USER_ID),0)) AS TotalPrice
	,(CASE WHEN (SELECT TOP 1 CONVERT(VARCHAR, DATETEME, 112) FROM M014_MerberBuy WHERE USER_ID = M012.USER_ID ORDER BY DATETEME DESC) > 
Isnull((SELECT TOP 1 BUSINESS_TIME FROM dbo.M010_BILL WHERE USER_NO = M012.USER_ID AND isnull(USER_NO,'')<>'' ORDER BY CREATE_DATETIME DESC),'19001224') THEN
(SELECT TOP 1 '日期:'+CONVERT(VARCHAR, DATETEME, 23)+' 金额:'+ LEFT('          ',9-LEN(CONVERT(VARCHAR,Convert(decimal(18,2),TOTAL)))) +CONVERT(VARCHAR,Convert(decimal(18,2),TOTAL)) FROM M014_MerberBuy WHERE USER_ID = M012.USER_ID ORDER BY DATETEME DESC) ELSE
(SELECT '日期:'+SUBSTRING(T.BUSINESS_TIME,1,4)+'-'+SUBSTRING(T.BUSINESS_TIME,5,2)+'-'+SUBSTRING(T.BUSINESS_TIME,7,2) +' 金额:'+ LEFT('          ',9-LEN(CONVERT(VARCHAR,-SUM(SELLING_PRICE*UNITS)))) + CONVERT(VARCHAR,-SUM(SELLING_PRICE*UNITS))
FROM dbo.M009_STORAGE M009 INNER JOIN (SELECT TOP 1 BILL_ID,BUSINESS_TIME,UPDATE_DATETIME FROM dbo.M010_BILL WHERE USER_NO = M012.USER_ID AND isnull(USER_NO,'')<>'' ORDER BY UPDATE_DATETIME DESC) T
ON T.BILL_ID = M009.BILL_ID AND M009.SELLING_PRICE>0 GROUP BY M009.BILL_ID,T.BUSINESS_TIME,T.UPDATE_DATETIME) END) AS AllPrice
FROM M012_MEMBER M012 
WHERE M012.TRANS_STATE <> '3'  ";

        #endregion

        #region GetUserNO+UserNameList

        private static string _getMemberrList_SQL = "Select USER_ID,USER_NO,USER_NAME,USER_ID+'#'+USER_NO as MemberIDNo,USER_NAME+'-'+USER_NO as MemberNameNo From M012_MEMBER Where 1=1 @@@ Order By USER_NAME";

        #endregion

        #region Add User

        private static string _addMember_Sql = @"INSERT INTO 
M012_MEMBER (CREATE_DATETIME,
    UPDATE_DATETIME,
    TRANS_STATE,
    UPDATER_ID , 
    TERMINAL_CD , 
    USER_ID , 
    USER_NO , 
    USER_NAME , 
    USER_SEX , 
    USER_BIRTHDAY , 
    USER_PHONE1 , 
    USER_PHONE2 , 
    USER_ADDRESS1 , 
    USER_ADDRESS2 , 
    USER_EMAIL , 
    USER_POST , 
    USER_PHOTO , 
    MEMO ) 
VALUES ( " + CONST.CurrentTime + "," + CONST.CurrentTime + ",'1',@UPDATER_ID , @TERMINAL_CD , @USER_ID , @USER_NO, @USER_NAME , @USER_SEX , @USER_BIRTHDAY , @USER_PHONE1 , @USER_PHONE2 , @USER_ADDRESS1 , @USER_ADDRESS2 , @USER_EMAIL , @USER_POST , @USER_PHOTO , @MEMO ) ";

        #endregion

        #region get User Photo

        private static string _getUserPhoto_Sql = @"SELECT USER_PHOTO FROM M012_MEMBER WHERE TRANS_STATE <> '3' AND USER_ID = @USER_ID";

        #endregion

        #region Delete User

        private static string _delUser_Sql = @"DELETE FROM M012_MEMBER WHERE USER_ID = @USER_ID";

        private static string _delUser_Logic_Sql = @"Update M012_MEMBER Set 
    [UPDATE_DATETIME] = CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''), 
    [TRANS_STATE] = '3' , 
    [UPDATER_ID] = @UPDATER_ID , 
    [TERMINAL_CD] = @TERMINAL_CD
WHERE USER_ID = @USER_ID ";

        #endregion

        #region 会员消费详单

        private static string _getMemberBuyDetail_SQL = @"select M010.BUSINESS_TIME
,M007.GOODS_NM1
,M009.SELLING_PRICE
,-M009.UNITS as UNITS
,M009.SELLING_PRICE*-M009.UNITS as AllPrice
,M012.USER_NAME
,M012.USER_NO
From dbo.M009_STORAGE M009 Inner Join dbo.M010_BILL M010
On M010.BILL_ID = M009.BILL_ID Left Join dbo.M007_GOODS M007
On M007.GOODS_ID = M009.GOODS_ID left join dbo.M012_MEMBER M012
On M012.USER_NO = M010.USER_NO 
Where M010.USER_NO = @USER_NO
Order By M010.BUSINESS_TIME ";

        #endregion

        #region 销售记录

        private static string _getSalesDetail_SQL = @"select M010.BUSINESS_TIME
,M007.GOODS_NM1
,(case when M009.SELLING_PRICE > 0 then M009.SELLING_PRICE else -M009.SELLING_PRICE end) as SELLING_PRICE
,-M009.UNITS as UNITS
,(case when M009.SELLING_PRICE*M009.UNITS>0 then M009.SELLING_PRICE*M009.UNITS else M009.SELLING_PRICE*-M009.UNITS end) as AllPrice
,M012.USER_NAME
,M012.USER_NO
,M010.BILL_ID
,isnull(M010.Preferential,0) as Preferential
,(select sum(SELLING_PRICE*-UNITS)-isnull(M010.Preferential,0) from M009_STORAGE where bill_id = M010.BILL_ID group by Bill_id) as BillCount
From dbo.M009_STORAGE M009 Inner Join dbo.M010_BILL M010
On M010.BILL_ID = M009.BILL_ID Left Join dbo.M007_GOODS M007
On M007.GOODS_ID = M009.GOODS_ID Left Join dbo.M012_MEMBER M012
On M012.USER_NO = M010.USER_NO and isnull(M012.User_No,'') !=''
Where M010.IN_OUT = '1' @@@
Order By M010.BUSINESS_TIME ,M010.BILL_ID ";

        #endregion

        #region 进货记录

        private static string _getBuyDetail_SQL = @"select M010.BUSINESS_TIME
,M007.GOODS_NM1
,M008.STOREROOM_NM
,(case when M009.BUYING_PRICE > 0 then M009.BUYING_PRICE else -M009.BUYING_PRICE end) as BUYING_PRICE
,M009.UNITS as UNITS
,(case when M009.BUYING_PRICE*M009.UNITS>0 then M009.BUYING_PRICE*M009.UNITS else M009.BUYING_PRICE*-M009.UNITS end) as AllPrice
,M010.BILL_ID
,(select sum(BUYING_PRICE*UNITS) from M009_STORAGE where bill_id = M010.BILL_ID group by Bill_id) as BillCount
From dbo.M009_STORAGE M009 Inner Join dbo.M010_BILL M010
On M010.BILL_ID = M009.BILL_ID Left Join dbo.M007_GOODS M007
On M007.GOODS_ID = M009.GOODS_ID left join M008_STOREROOM M008 
	On M008.STOREROOM_ID = M009.STOREROOM_ID
Where M010.IN_OUT = '0' @@@
Order By M010.BUSINESS_TIME ,M010.BILL_ID ";

        #endregion

        #region 盘库记录

        private static string _getStocktakingDetail_SQL = @"select M010.BUSINESS_TIME
,M007.GOODS_NM1
,M008.STOREROOM_NM
,M009.UNITS as UNITS
,M007.UNITS as NowUNITS
,M010.BILL_ID
From dbo.M009_STORAGE M009 Inner Join dbo.M010_BILL M010
On M010.BILL_ID = M009.BILL_ID Left Join dbo.M007_GOODS M007
On M007.GOODS_ID = M009.GOODS_ID left join M008_STOREROOM M008 
	On M008.STOREROOM_ID = M009.STOREROOM_ID
Where M010.IN_OUT = '2' @@@
Order By M010.BUSINESS_TIME ,M010.BILL_ID,M007.GOODS_ID";

        #endregion

        #region 消费单合计

        private static string _getBillTotalPrice_SQL = @"Select M010.BUSINESS_TIME
,(SELECT LEFT(GOODS_NM,LEN(GOODS_NM)-1) FROM (Select 
(Select M007.GOODS_NM1+'、' 
From M007_GOODS M007 Inner Join M009_STORAGE M009 
On M007.GOODS_ID = M009.GOODS_ID And M009.BILL_ID = M010.BILL_ID 
Order BY M009.BILL_ID,M007.GOODS_ID FOR XML PATH('')) as GOODS_NM) A) as GOODS_NM1

,(SELECT LEFT(SELLING_PRICE,LEN(SELLING_PRICE)-1) FROM (Select 
(Select convert(varchar,(case when SELLING_PRICE < 0 then 0 else SELLING_PRICE end))+'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY BILL_ID,GOODS_ID FOR XML PATH('')) as SELLING_PRICE) A) as SELLING_PRICE

,(SELECT LEFT(UNITS,LEN(UNITS)-1) FROM (Select 
(Select convert(varchar,-UNITS)+'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY BILL_ID,GOODS_ID FOR XML PATH('')) as UNITS) A) as UNITS

,(SELECT LEFT(AllPrice,LEN(AllPrice)-1) FROM (Select 
(Select convert(varchar,(case when SELLING_PRICE < 0 then 0 else SELLING_PRICE end)*-UNITS) +'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY BILL_ID,GOODS_ID FOR XML PATH('')) as AllPrice) A) as AllPrice
,M012.USER_NAME
,M012.USER_NO
,M010.BILL_ID
,isnull(M010.Preferential,'') as Preferential
,(select sum(SELLING_PRICE*-UNITS) from M009_STORAGE where bill_id = M010.BILL_ID group by Bill_id) as BillCount
From M010_BILL M010 Left Join M012_MEMBER M012
On M012.USER_NO = M010.USER_NO and isnull(M012.User_No,'') !=''
Where M010.IN_OUT = '1'  And M010.Bill_Id in (select Bill_Id From dbo.M009_STORAGE)
@@@
Order By M010.BUSINESS_TIME ,M010.BILL_ID";

        #endregion 

        #region 盘库

        private static string _getStocktakingTotal_SQL = @"select distinct * from (
Select M010.BUSINESS_TIME
,M008.STOREROOM_NM
,(SELECT LEFT(GOODS_NM,LEN(GOODS_NM)-1) FROM (Select 
(Select M007.GOODS_NM1+'、' 
From M007_GOODS M007 Inner Join M009_STORAGE M009 
On M007.GOODS_ID = M009.GOODS_ID And M009.BILL_ID = M010.BILL_ID 
Order BY M010.BUSINESS_TIME, M009.BILL_ID,M007.GOODS_ID FOR XML PATH('')) as GOODS_NM) A) as GOODS_NM1

,(SELECT LEFT(UNITS,LEN(UNITS)-1) FROM (Select 
(Select convert(varchar,UNITS)+'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY M010.BUSINESS_TIME,BILL_ID,GOODS_ID FOR XML PATH('')) as UNITS) A) as UNITS

,(SELECT LEFT(A.UNITS,LEN(A.UNITS)-1) FROM (Select 
(Select convert(varchar,M007_.UNITS)+'、' 
From M007_GOODS M007_ Inner Join M009_STORAGE M009_ 
On M007_.GOODS_ID = M009_.GOODS_ID And M009_.BILL_ID = M010.BILL_ID 
Where M009_.BILL_ID = M010.BILL_ID 
Order BY M010.BUSINESS_TIME,M009.BILL_ID,M007_.GOODS_ID FOR XML PATH('')) as UNITS) A) as NowUNITS

,M010.BILL_ID
From M010_BILL M010 left join M009_STORAGE M009
	On M009.BILL_ID = M010.BILL_ID left join M008_STOREROOM M008 
	On M008.STOREROOM_ID = M009.STOREROOM_ID
Where M010.IN_OUT = '2'  And M010.Bill_Id in (select Bill_Id From dbo.M009_STORAGE)
@@@
) A Order By BUSINESS_TIME ,BILL_ID";

        #endregion

        #region 进货明细

        private static string _getBuyTotailPrice_SQL = @"Select distinct * from (select M010.BUSINESS_TIME
,M008.STOREROOM_NM
,(SELECT LEFT(GOODS_NM,LEN(GOODS_NM)-1) FROM (Select 
(Select M007.GOODS_NM1+'、' 
From M007_GOODS M007 Inner Join M009_STORAGE M009 
On M007.GOODS_ID = M009.GOODS_ID And M009.BILL_ID = M010.BILL_ID 
Order BY M009.BILL_ID,M007.GOODS_ID FOR XML PATH('')) as GOODS_NM) A) as GOODS_NM1

,(SELECT LEFT(BUYING_PRICE,LEN(BUYING_PRICE)-1) FROM (Select 
(Select convert(varchar,(case when BUYING_PRICE < 0 then 0 else BUYING_PRICE end))+'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY BILL_ID,GOODS_ID FOR XML PATH('')) as BUYING_PRICE) A) as BUYING_PRICE

,(SELECT LEFT(UNITS,LEN(UNITS)-1) FROM (Select 
(Select convert(varchar,UNITS)+'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY BILL_ID,GOODS_ID FOR XML PATH('')) as UNITS) A) as UNITS

,(SELECT LEFT(AllPrice,LEN(AllPrice)-1) FROM (Select 
(Select convert(varchar,(case when BUYING_PRICE < 0 then 0 else BUYING_PRICE end)*UNITS) +'、' 
From M009_STORAGE Where BILL_ID = M010.BILL_ID 
Order BY BILL_ID,GOODS_ID FOR XML PATH('')) as AllPrice) A) as AllPrice
,M010.BILL_ID
,(select sum(BUYING_PRICE*UNITS) from M009_STORAGE where bill_id = M010.BILL_ID group by Bill_id) as BillCount
From M010_BILL M010 left join M009_STORAGE M009
	On M009.BILL_ID = M010.BILL_ID left join M008_STOREROOM M008 
	On M008.STOREROOM_ID = M009.STOREROOM_ID
Where M010.IN_OUT = '0' And M010.Bill_Id in (select Bill_Id From dbo.M009_STORAGE)
@@@) A
Order By BUSINESS_TIME ,BILL_ID";

        #endregion

        #region Update User

        private static string _updateMember_Sql = @"UPDATE 
     M012_MEMBER 
SET
     [UPDATE_DATETIME] = CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''), 
     [TRANS_STATE] = '2' , 
     [UPDATER_ID] = @UPDATER_ID , 
     [TERMINAL_CD] = @TERMINAL_CD , 
     [USER_NO] = @USER_NO , 
     [USER_NAME] = @USER_NAME , 
     [USER_SEX] = @USER_SEX , 
     [USER_BIRTHDAY] = @USER_BIRTHDAY , 
     [USER_PHONE1] = @USER_PHONE1 , 
     [USER_PHONE2] = @USER_PHONE2 , 
     [USER_ADDRESS1] = @USER_ADDRESS1 , 
     [USER_ADDRESS2] = @USER_ADDRESS2 , 
     [USER_EMAIL] = @USER_EMAIL , 
     [USER_POST] = @USER_POST , 
     [USER_PHOTO] = @USER_PHOTO , 
     [MEMO] = @MEMO 
WHERE 
     USER_ID=@USER_ID ";

        #endregion

        #region check User Exist

        private static string _getMemberID_Sql = @"SELECT USER_ID FROM M012_MEMBER WHERE 1=1 ";

        private static string _checkUserNoIDExist_Sql = @"SELECT USER_NAME FROM M012_MEMBER WHERE USER_NO=@USER_NO And USER_ID <> @USER_ID";

        #endregion

        #region 取得正在使用的会员

        private static string _getMemberUsed = @"Select USER_NO From M010_BILL Where 1=1 ";

        #endregion

        private static string _checkIsUseUserNo_SQL = @"SELECT USER_NO FROM M012_MEMBER
WHERE (USER_NO IN (SELECT DISTINCT USER_NO FROM M010_BILL WHERE USER_NO <> '')
OR USER_NO IN (SELECT DISTINCT USER_ID FROM dbo.M014_MerberBuy WHERE USER_ID <> ''))
AND USER_NO = @USER_NO ";

        #endregion

        #region 属性        

        /// <summary>
        /// GetUserList
        /// </summary>
        public static string GetUserList
        {
            get { return _getUserList_Sql; }
        }

        /// <summary>
        /// 添加会员
        /// </summary>
        public static string AddMember
        {
            get { return _addMember_Sql; }
        }

        /// <summary>
        /// GetUserPhoto
        /// </summary>
        public static string GetUserPhoto
        {
            get { return _getUserPhoto_Sql; }
        }

        /// <summary>
        /// DelUser
        /// </summary>
        public static string DelUser
        {
            get { return _delUser_Sql; }
        }

        public static string DelUser_Logic
        {
            get { return _delUser_Logic_Sql; }
        }

        /// <summary>
        /// UpdateUser
        /// </summary>
        public static string UpdateMember
        {
            get { return _updateMember_Sql; }
        }

        public static string GetMemberBuyDetail
        {
            get { return _getMemberBuyDetail_SQL; }
        }

        public static string GetSalesDetail
        {
            get { return _getSalesDetail_SQL; }
        }

        public static string GetBuyDetail
        {
            get { return _getBuyDetail_SQL; }
        }

        /// <summary>
        /// 盘库汇总
        /// </summary>
        public static string GetStocktakingTotal
        {
            get { return _getStocktakingTotal_SQL; }
        }

        /// <summary>
        /// 盘库明细SQL
        /// </summary>
        public static string GetStocktakingDetail
        {
            get { return _getStocktakingDetail_SQL; }
        }

        public static string GetBillTotalPrice
        {
            get { return _getBillTotalPrice_SQL; }
        }

        public static string GetBuyTotailPrice
        {
            get { return _getBuyTotailPrice_SQL; }
        }

        public static string GetMemberrList
        {
            get { return _getMemberrList_SQL; }
        }

        /// <summary>
        /// 取得会员编号
        /// </summary>
        public static string GetMemberID
        {
            get { return _getMemberID_Sql; }
        }

        public  static string CheckUserNoIDExist
        {
            get { return _checkUserNoIDExist_Sql; }
        }

        public static string CheckIsUseUserNo
        {
            get { return _checkIsUseUserNo_SQL; }
        }

        /// <summary>
        /// 取得正在使用的会员
        /// </summary>
        public static string GetMemberUsed
        {
            get { return _getMemberUsed; }
        }   

        #endregion
    }
}
